﻿using IFRS.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace IFRS.DataAccess.DAL
{
    public class SPR_GLACCOUNTPOSTING
    {
        public DataTable SEARCH_ALL(GLAccountPosting glAccountPosting, Paging paging, bool IFRS_ALLBRANCH, string sIFRS_COST_CENTER, ref int iRecordCount)
        {
            DataTable dt = new DataTable();
            IFRS_Utility Utility = new IFRS_Utility();
            List<SqlParameter> lstParams = new List<SqlParameter>();
            try
            {
                lstParams.Add(new SqlParameter("@RecordCount", SqlDbType.Int));
                lstParams[0].Direction = ParameterDirection.Output;
                lstParams.Add(new SqlParameter("@PageIndex", paging.PageIndex));
                lstParams.Add(new SqlParameter("@PageSize", paging.PageSize));
                lstParams.Add(new SqlParameter("@Sorting", paging.Sorting));
                lstParams.Add(new SqlParameter("@isAllBranch", IFRS_ALLBRANCH));
                lstParams.Add(new SqlParameter("@sCostCenter", sIFRS_COST_CENTER));

                lstParams.Add(new SqlParameter("@GLGroupID", glAccountPosting.GLGroupID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GLAccountID", glAccountPosting.GLAccountID.AsDBValue()));
                dt = Utility.StoredProcDataTable("SPR_GLACCOUNTPOSTING_SEARCHALL", lstParams.ToArray());

                iRecordCount = (int)lstParams[0].Value;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return dt;
        }

        public DataTable SEARCH_BYID(int GLAccountPostingID)
        {
            DataTable dt = new DataTable();
            IFRS_Utility Utility = new IFRS_Utility();
            List<SqlParameter> lstParams = new List<SqlParameter>();
            try
            {
                lstParams.Add(new SqlParameter("@GLAccountPostingID", GLAccountPostingID));
                dt = Utility.StoredProcDataTable("SPR_GLACCOUNTPOSTING_SEARCHBYID", lstParams.ToArray());
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return dt;
        }

        public int INSERT(GLAccountPosting glAccountPosting)
        {
            int iNum = 0;
            IFRS_Utility Utility = new IFRS_Utility();
            List<SqlParameter> lstParams = new List<SqlParameter>();

            try
            {
                lstParams.Add(new SqlParameter("@GLAccountPostingID", SqlDbType.Int));
                lstParams[0].Direction = ParameterDirection.Output;
                lstParams.Add(new SqlParameter("@GLGroupID", glAccountPosting.GLGroupID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GLAccountID", glAccountPosting.GLAccountID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_ADJ_INT_INC_PL_ID", glAccountPosting.GL_ADJ_INT_INC_PL_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_ADJ_FV_BS_ID", glAccountPosting.GL_ADJ_FV_BS_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_ADJ_INT_INC_ELCLS_PL_ID", glAccountPosting.GL_ADJ_INT_INC_ELCLS_PL_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_ADJ_CST_BS_ID", glAccountPosting.GL_ADJ_CST_BS_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_REV_CST_PL_ID", glAccountPosting.GL_REV_CST_PL_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_ADJ_FEE_BS_ID", glAccountPosting.GL_ADJ_FEE_BS_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_REV_FEE_PL_ID", glAccountPosting.GL_REV_FEE_PL_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_SUBSEQ_ADJ_CST_BS_ID", glAccountPosting.GL_SUBSEQ_ADJ_CST_BS_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_SUBSEQ_ADJ_FEE_BS_ID", glAccountPosting.GL_SUBSEQ_ADJ_FEE_BS_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_IMP_LOSS_PL_ID", glAccountPosting.GL_IMP_LOSS_PL_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_ALLW_BAD_DEBT_PL_ID", glAccountPosting.GL_ALLW_BAD_DEBT_PL_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_WOFF_ADJ_FV_BS_ID", glAccountPosting.GL_WOFF_ADJ_FV_BS_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_WOFF_ADJ_FEE_BS_ID", glAccountPosting.GL_WOFF_ADJ_FEE_BS_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_WOFF_ADJ_CST_BS_ID", glAccountPosting.GL_WOFF_ADJ_CST_BS_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_DAY1_FV_LOSS_PL_ID", glAccountPosting.GL_DAY1_FV_LOSS_PL_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_DAY1_ADJ_FV_BS_ID", glAccountPosting.GL_DAY1_ADJ_FV_BS_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_WOFF_INT_DISC_LN_ID", glAccountPosting.GL_WOFF_INT_DISC_LN_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_DAY1_DEF_EXP_STAFF_BS_ID", glAccountPosting.GL_DAY1_DEF_EXP_STAFF_BS_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_AMC_EMP_CST_PL_ID", glAccountPosting.GL_AMC_EMP_CST_PL_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_AMC_DEF_EXP_STAFF_BS_ID", glAccountPosting.GL_AMC_DEF_EXP_STAFF_BS_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_SUBSIDY_INT_REC_BS_ID", glAccountPosting.GL_SUBSIDY_INT_REC_BS_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_SUBSIDY_INT_INC_PL_ID", glAccountPosting.GL_SUBSIDY_INT_INC_PL_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_ACC_SUBSIDY_REV_CHG_ID", glAccountPosting.GL_ACC_SUBSIDY_REV_CHG_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_ADJ_SUBSIDY_REV_CHG_ID", glAccountPosting.GL_ADJ_SUBSIDY_REV_CHG_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_LOSS_IMP_INV_PL_ID", glAccountPosting.GL_LOSS_IMP_INV_PL_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_COMMISSION_ID", glAccountPosting.GL_COMMISSION_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_INV_AMT_CHG_ID", glAccountPosting.GL_INV_AMT_CHG_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_DAY1_LOSS_PL_ID", glAccountPosting.GL_DAY1_LOSS_PL_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_CHG_UNREALIZED_AFS_PL_ID", glAccountPosting.GL_CHG_UNREALIZED_AFS_PL_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_ACCUM_FVTOCI_PL_ID", glAccountPosting.GL_ACCUM_FVTOCI_PL_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@CreatedDate", glAccountPosting.CreatedDate.AsDBValue()));
                lstParams.Add(new SqlParameter("@CreatedBy", glAccountPosting.CreatedBy.AsDBValue()));
                lstParams.Add(new SqlParameter("@CreatedCostCenter", glAccountPosting.CreatedCostCenter.AsDBValue()));
                lstParams.Add(new SqlParameter("@CreatedBranch", glAccountPosting.CreatedBranch.AsDBValue()));
                lstParams.Add(new SqlParameter("@UpdatedDate", glAccountPosting.UpdatedDate.AsDBValue()));
                lstParams.Add(new SqlParameter("@UpdatedBy", glAccountPosting.UpdatedBy.AsDBValue()));
                lstParams.Add(new SqlParameter("@UpdatedCostCenter", glAccountPosting.UpdatedCostCenter.AsDBValue()));
                lstParams.Add(new SqlParameter("@UpdatedBranch", glAccountPosting.UpdatedBranch.AsDBValue()));

                Utility.ExecuteStoredProc("SPR_GLACCOUNTPOSTING_INSERT", lstParams.ToArray());

                iNum = (int)lstParams[0].Value;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return iNum;
        }

        public int UPDATE(GLAccountPosting glAccountPosting)
        {
            int iNum = 0;
            IFRS_Utility Utility = new IFRS_Utility();
            List<SqlParameter> lstParams = new List<SqlParameter>();
            try
            {
                lstParams.Add(new SqlParameter("@GLAccountPostingID", glAccountPosting.GLAccountPostingID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GLGroupID", glAccountPosting.GLGroupID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GLAccountID", glAccountPosting.GLAccountID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_ADJ_INT_INC_PL_ID", glAccountPosting.GL_ADJ_INT_INC_PL_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_ADJ_FV_BS_ID", glAccountPosting.GL_ADJ_FV_BS_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_ADJ_INT_INC_ELCLS_PL_ID", glAccountPosting.GL_ADJ_INT_INC_ELCLS_PL_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_ADJ_CST_BS_ID", glAccountPosting.GL_ADJ_CST_BS_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_REV_CST_PL_ID", glAccountPosting.GL_REV_CST_PL_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_ADJ_FEE_BS_ID", glAccountPosting.GL_ADJ_FEE_BS_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_REV_FEE_PL_ID", glAccountPosting.GL_REV_FEE_PL_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_SUBSEQ_ADJ_CST_BS_ID", glAccountPosting.GL_SUBSEQ_ADJ_CST_BS_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_SUBSEQ_ADJ_FEE_BS_ID", glAccountPosting.GL_SUBSEQ_ADJ_FEE_BS_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_IMP_LOSS_PL_ID", glAccountPosting.GL_IMP_LOSS_PL_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_ALLW_BAD_DEBT_PL_ID", glAccountPosting.GL_ALLW_BAD_DEBT_PL_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_WOFF_ADJ_FV_BS_ID", glAccountPosting.GL_WOFF_ADJ_FV_BS_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_WOFF_ADJ_FEE_BS_ID", glAccountPosting.GL_WOFF_ADJ_FEE_BS_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_WOFF_ADJ_CST_BS_ID", glAccountPosting.GL_WOFF_ADJ_CST_BS_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_DAY1_FV_LOSS_PL_ID", glAccountPosting.GL_DAY1_FV_LOSS_PL_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_DAY1_ADJ_FV_BS_ID", glAccountPosting.GL_DAY1_ADJ_FV_BS_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_WOFF_INT_DISC_LN_ID", glAccountPosting.GL_WOFF_INT_DISC_LN_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_DAY1_DEF_EXP_STAFF_BS_ID", glAccountPosting.GL_DAY1_DEF_EXP_STAFF_BS_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_AMC_EMP_CST_PL_ID", glAccountPosting.GL_AMC_EMP_CST_PL_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_AMC_DEF_EXP_STAFF_BS_ID", glAccountPosting.GL_AMC_DEF_EXP_STAFF_BS_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_SUBSIDY_INT_REC_BS_ID", glAccountPosting.GL_SUBSIDY_INT_REC_BS_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_SUBSIDY_INT_INC_PL_ID", glAccountPosting.GL_SUBSIDY_INT_INC_PL_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_ACC_SUBSIDY_REV_CHG_ID", glAccountPosting.GL_ACC_SUBSIDY_REV_CHG_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_ADJ_SUBSIDY_REV_CHG_ID", glAccountPosting.GL_ADJ_SUBSIDY_REV_CHG_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_LOSS_IMP_INV_PL_ID", glAccountPosting.GL_LOSS_IMP_INV_PL_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_COMMISSION_ID", glAccountPosting.GL_COMMISSION_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_INV_AMT_CHG_ID", glAccountPosting.GL_INV_AMT_CHG_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_DAY1_LOSS_PL_ID", glAccountPosting.GL_DAY1_LOSS_PL_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_CHG_UNREALIZED_AFS_PL_ID", glAccountPosting.GL_CHG_UNREALIZED_AFS_PL_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@GL_ACCUM_FVTOCI_PL_ID", glAccountPosting.GL_ACCUM_FVTOCI_PL_ID.AsDBValue()));
                lstParams.Add(new SqlParameter("@UpdatedDate", glAccountPosting.UpdatedDate.AsDBValue()));
                lstParams.Add(new SqlParameter("@UpdatedBy", glAccountPosting.UpdatedBy.AsDBValue()));
                lstParams.Add(new SqlParameter("@UpdatedCostCenter", glAccountPosting.UpdatedCostCenter.AsDBValue()));
                lstParams.Add(new SqlParameter("@UpdatedBranch", glAccountPosting.UpdatedBranch.AsDBValue()));

                iNum = (int)Utility.ExecuteStoredProc("SPR_GLACCOUNTPOSTING_UPDATE", lstParams.ToArray());
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return iNum;
        }

        public int DELETE(GLAccountPosting glAccountPosting)
        {
            int iNum = 0;
            IFRS_Utility Utility = new IFRS_Utility();
            List<SqlParameter> lstParams = new List<SqlParameter>();
            try
            {
                lstParams.Add(new SqlParameter("@GLAccountPostingID", glAccountPosting.GLAccountPostingID.AsDBValue()));
                lstParams.Add(new SqlParameter("@UpdatedDate", glAccountPosting.UpdatedDate.AsDBValue()));
                lstParams.Add(new SqlParameter("@UpdatedBy", glAccountPosting.UpdatedBy.AsDBValue()));
                lstParams.Add(new SqlParameter("@UpdatedCostCenter", glAccountPosting.UpdatedCostCenter.AsDBValue()));
                lstParams.Add(new SqlParameter("@UpdatedBranch", glAccountPosting.UpdatedBranch.AsDBValue()));
                iNum = (int)Utility.ExecuteStoredProc("SPR_GLACCOUNTPOSTING_DELETE", lstParams.ToArray());
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return iNum;
        }
    }
}